#!/usr/bin/env python3
# encoding=utf-8

import os
import datetime
from copy import copy
from openpyxl import load_workbook
from excel_by_xlwings import get_img_from_xw_sheet
from word_process import word_to_image
from commom_api import (get_excel_setting_info, is_weekend_day
                        , get_doc_dirpath, email_merge_by_product
                        , get_excel_name_by_emailinfo, excel_rename_list_update)

import logging
logger = logging.getLogger(__name__)

def find_last_row_by_openpyxl(sheet):
    for lastrow in range(sheet.max_row, 1, -1):
        val = sheet.cell(row=lastrow, column=1).value
        if val == None:
            continue
        return lastrow
    
def filter_emailinfo_by_openpyxl(emailinfo):
    newinfo = []
    if len(emailinfo) == 0:
        return newinfo
    emailinfo = sorted(emailinfo, key=lambda x: x["name"])
    emaildictinfo = email_merge_by_product(emailinfo)
    for key, maillist in emaildictinfo.items():
    # for onemail in emailinfo:
        xlslist = get_excel_name_by_emailinfo(key, withspcode=(maillist[0]['code'], maillist[0]['name']))
        for xlsname in xlslist:
            if xlsname == None:
                continue
            orgexcel = os.path.join(get_doc_dirpath(), xlsname)
            wb = load_workbook(orgexcel, data_only=True)
            if wb == None:
                logging.error(f"open {orgexcel} error")
                continue
            for sheet in wb.worksheets:
                if sheet.title.find('（周）') <= 0:
                    continue
                lastrow = find_last_row_by_openpyxl(sheet)
                maxdate = sheet.cell(row=lastrow, column=1).value
                logger.info(f"get maxrow {xlsname}:{lastrow} is {maxdate}")
                for onemail in maillist:
                    emaildate = datetime.datetime.strptime(onemail['date'], "%Y-%m-%d")
                    if emaildate <= maxdate:
                        logger.info(f"filter {onemail} by range {xlsname}")
                        continue
                    if onemail not in newinfo:
                        newinfo.append(onemail)
            wb.close()
    # newinfo = sorted(newinfo, key=lambda x: x["name"])
    # newinfo = sorted(newinfo, key=lambda x: datetime.datetime.strptime(x["date"], "%Y-%m-%d"))
    return newinfo

def change_xlsinfo_by_openpyxl_sheet(sheet):
    xlsinfo = {}
    xlsbase = get_excel_setting_info()
    maxcol = sheet.max_column
    for col in range(1, maxcol + 1):
        cval = sheet.cell(row=xlsbase['headerline'], column=col).value
        # print(f'col{col} is {cval}')
        if cval == None:
            logger.debug(f"col max {maxcol} {col} none data")
            break
        for key, val in xlsbase.items():
            if cval == val:
                xlsinfo[key] = (col, val)
    for key, val in xlsbase.items():
        if key in xlsinfo:
            continue
        xlsinfo[key] = val
    
    maxrow = find_last_row_by_openpyxl(sheet)
    for row in range(0, maxrow):
        cval = sheet.cell(row=maxrow - row, column=1).value
        # print(f'row{row} is {cval}')
        if cval != None:
            # logger.debug(f"row max {maxrow} {row} none data")
            break
    maxrow = maxrow - row
    xlsinfo['flexiblepos'] = [xlsbase['headerline']+1, col, maxrow]

    mindate = sheet.cell(row=xlsbase['headerline']+1, column=1).value.replace(tzinfo=None)
    maxdate = sheet.cell(row=maxrow, column=1).value.replace(tzinfo=None)
    xlsinfo['daterange'] = [mindate, maxdate]
    return xlsinfo

def deal_with_openpyxl_sheet_data(wb, xlsinfo, onemail, codeinfo):
    sheet = wb[xlsinfo['sheetname']]
    flexiblecol = xlsinfo['flexiblepos'][1] + 1
    lastrow = xlsinfo['flexiblepos'][2]

    for col in range(1, flexiblecol):
        onekey = None
        for key, val in xlsinfo.items():
            try:
                if col == val[0]:
                    onekey = key
            except:
                continue
        # logger.info(onekey)
        oldcell = sheet.cell(row=lastrow, column=col)
        oldval = oldcell.value
        if isinstance(oldval, str) and oldval.startswith('='):
            oldval = oldval.replace(str(lastrow), str(lastrow+1))
            oldval = oldval.replace(str(lastrow-1), str(lastrow))
            sheet.cell(row=lastrow+1, column=col).value = oldval
        else:
            if onekey != None:
                if onekey in onemail.keys():
                    if onekey == 'date':
                        # 不能用字符串，必须用时间，不然count失效
                        sheet.cell(row=lastrow+1, column=col).value = datetime.datetime.strptime(onemail[onekey], "%Y-%m-%d")
                        if len(xlsinfo['daterange']) != 3:
                            xlsinfo['daterange'].append(onemail[onekey])
                        else:
                            xlsinfo['daterange'][2] = onemail[onekey]
                    else:
                        sheet.cell(row=lastrow+1, column=col).value = float(onemail[onekey])
                elif onekey in codeinfo.keys():
                    # logger.info('get from code')
                    for date, val in codeinfo[onekey]:
                        if date == onemail['date']:
                            sheet.cell(row=lastrow+1, column=col).value = val
            else:
                sheet.cell(row=lastrow+1, column=col).value = oldval
        
        # sheet.cell(row=lastrow+1, column=col).font = copy(oldcell.font)
        # sheet.cell(row=lastrow+1, column=col).fill = copy(oldcell.fill)
        # sheet.cell(row=lastrow+1, column=col).border = copy(oldcell.border)
        # sheet.cell(row=lastrow+1, column=col).alignment = copy(oldcell.alignment)
        sheet.cell(row=lastrow+1, column=col)._style = copy(oldcell._style)
        sheet.cell(row=lastrow+1, column=col).number_format = copy(oldcell.number_format)

    cb2 = sheet['B2'].value.replace(str(lastrow), str(lastrow+1))
    sheet['B2'].value = cb2
    logger.info(f'{cb2} for %s' % onemail['date'])
    xlsinfo['flexiblepos'][2] = lastrow + 1
    wb.save(xlsinfo['excel'])
    # wb.RefreshAll()                # 刷新数据透视表、Power Query等
    return xlsinfo

def change_chart_postion(chart):
    anchor = chart.anchor
    if hasattr(anchor, '_from'):
        anchor._from.row += 1
    if hasattr(anchor, 'to'):
        anchor.to.row += 1

def deal_with_openpyxl_sheet_oldchart(wb, xlsinfo):
    lastrow = xlsinfo['flexiblepos'][2]
    sheet = wb[xlsinfo['sheetname']]
    for chart in sheet._charts:
        # print(chart.title.tx.rich.p[0].r[0].t)
        change_chart_postion(chart)
        for series in chart.ser:
            # 若为散点图或XY图表 x 轴
            if hasattr(series, 'xVal') and series.xVal:
                sernumref = series.xVal.numRef.f
                series.xVal.numRef.f = sernumref.replace(str(lastrow-1), str(lastrow))
            # 折线图 x 轴
            if hasattr(series, 'cat') and series.cat:
                sernumref = series.cat.numRef.f
                series.cat.numRef.f = sernumref.replace(str(lastrow-1), str(lastrow))
            # 普通图表 y 轴
            if hasattr(series, 'val') and series.val:
                sernumref = series.val.numRef.f
                series.val.numRef.f = sernumref.replace(str(lastrow-1), str(lastrow))
    wb.save(xlsinfo['excel'])

def duil_with_file_by_openpyxl(excelpath, maillist, codeinfo):
    renamefile = {}
    wordinfo = []
    wb = load_workbook(excelpath)
    if wb == None:
        logging.error(f"open {excelpath} error")
        return renamefile
    for sheet in wb.worksheets:
        xlsinfo = change_xlsinfo_by_openpyxl_sheet(sheet)
        xlsinfo['excel'] = excelpath
        xlsinfo['sheetname'] = sheet.title
        xlsinfo['code'] = maillist[0]['code']
        xlsinfo['filltable'] = True
        xlsinfo['fillpicture'] = True
        logger.info(f"{sheet.title}")
        if sheet.title.find('（日）') > 0:
            for one in maillist:    
                xlsinfo['code'] = one['code']
                xlsinfo = deal_with_openpyxl_sheet_data(wb, xlsinfo, one, codeinfo)
                deal_with_openpyxl_sheet_oldchart(wb, xlsinfo)
            sheet.Visible = False
            if xlsinfo['code'] in xlsinfo['日净值']:
                xlsinfo['fillpicture'] = False
                oneword = get_img_from_xw_sheet(wb, xlsinfo)
                if oneword and oneword not in wordinfo:
                    wordinfo.append(oneword)
        elif sheet.title.find('（周）') > 0:
            weekend = []
            for one in maillist:    
                if is_weekend_day(one['date']) == False:
                    continue
                weekend.append(one)
            
            for one in weekend:
                xlsinfo = deal_with_openpyxl_sheet_data(wb, xlsinfo, one, codeinfo)
                deal_with_openpyxl_sheet_oldchart(wb, xlsinfo)
                
            if len(weekend) == 0 and xlsinfo['code'] not in xlsinfo['日净值']:
                continue
            # 日净值的情况要更新周图
            if xlsinfo['code'] in xlsinfo['日净值']:
                xlsinfo['filltable'] = False
            oneword = get_img_from_xw_sheet(xlsinfo)
            if oneword and oneword not in wordinfo:
                wordinfo.append(oneword)
        else:
            continue
        renamefile = excel_rename_list_update(xlsinfo, renamefile)
    wb.close()

    for oneword in wordinfo:
        if len(oneword) != 2:
            continue
        word_to_image(oneword[0], oneword[1])
    return renamefile
